Skip to main content

Adding New Tables to Supabase

This guide shows you how to add a new table to your Supabase database and use it in your SvelteBolt app.

1. Create the Migration​

Create a new migration file in supabase/migrations/:

-- Example: 20240529000000_create_posts_table.sql
CREATE TABLE posts (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  title text NOT NULL,
  content text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view their own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

2. Apply the Migration​

bun x supabase db push --project-id $SUPABASE_PROJECT_ID
tip

You can also copy the migration file to your Supabase dashboard under Database > SQL Editor and run it there. But using the CLI is recommended for version control and consistency.

3. Generate Types​

Update your TypeScript types:

bun x supabase gen types --lang=typescript --project-id $SUPABASE_PROJECT_ID > database.types.ts

or you can run :

bun update-types

4. Create Server Functions​

Add CRUD functions in src/lib/server/database/:

// posts.ts
import { supabase } from "./supabase";
import type { Database } from "../../../database.types";

type Post = Database["public"]["Tables"]["posts"]["Row"];

export async function createPost(
  userId: string,
  title: string,
  content: string
) {
  const { data, error } = await supabase
    .from("posts")
    .insert({ user_id: userId, title, content })
    .select()
    .single();

  if (error) throw error;
  return data;
}

export async function getUserPosts(userId: string) {
  const { data, error } = await supabase
    .from("posts")
    .select("*")
    .eq("user_id", userId);

  if (error) throw error;
  return data;
}

5. Use in Your Routes​

// src/routes/posts/+page.server.ts
import { getUserPosts } from "$lib/server/database/posts";

export async function load({ locals }) {
  if (!locals.user) {
    return { posts: [] };
  }

  const posts = await getUserPosts(locals.user.id);
  return { posts };
}

That's it! Your new table is ready to use. The key steps are: create migration, push to DB, generate types, create server functions, and use in your routes.